*********BY CITY
cap cd "C:\Users\srb834\Dropbox\Firm Customer Bases\Data\"
clear all
use exclusivity_fraction_sales_category_by_firm_city_state_time

nsplit yyyyqq, digits(4 2)
rename yyyyqq1 year

collapse frac, by(gvkey city year)
rename city city
replace city = upper(city)

compress
save fraction_sales_category_by_firm_year_city, replace


********************************
cap cd "C:\Users\srb834\Dropbox\Firm Customer Bases\Data"
clear all
use acbycities


replace chshare = chshare/2

**Just keep one-year churn
keep if year2==year1+1
drop year1
rename year2 year

replace city = upper(city)
rename city city
drop sindex

order gvkey city year
sort gvkey city year

***Some sample restrictions on tiny firm-city-years
**Drop no spending states
drop if sum_amount1==. | sum_amount2==.
**Minimum number of customers
drop if customers<50
**Minimum spending
drop if sum_amount1<1000
drop if sum_amount2<1000

**Drop some crazy churn?
drop if chshare<=0.1|chshare>=1

***Merge in local spending shares within category
merge 1:1 gvkey city year using fraction_sales_category_by_firm_year_city
drop if _merge==2
drop _merge

**Drop some crazy sale fractions? 
drop if frac_of_category_sales<.001

****Merge firm names
merge m:1 gvkey using "firm_names_gvkeys.dta"
keep if _merge==3
drop _merge

****Merge firm cats
merge m:1 gvkey using "gvkey_category.dta"
keep if _merge==3
drop _merge

/****Merge compustat
merge m:1 year gvkey using "..\Data\Compustat_NA_2010_2019_annual.dta"
keep if _merge==3
drop _merge

****Merge compustat
merge m:1 year gvkey using "..\Data\Compustat_NA_2005_2020_annual_intan.dta"
keep if _merge==3
drop _merge

gen sic1 = floor(sic/1000)
gen sic2 = floor(sic/100)
*/

encode city, gen(city_code)
encode category, gen(category_code)

gen Store_Type = "Long-term Contracts" if category=="Utilities" | category=="Consumer Telecom"
replace Store_Type = "Loyalty or Infrequent" if category=="Hotels, Rentals" |category=="Airlines" | category=="Clothing & Shoes"| category=="Misc Services"|category=="Online Services & Tech"
replace Store_Type = "Regular Purchases" if category=="Restaurants" |category=="Convenience Stores" | category=="General Merchandise"| category=="Groceries"| category=="Entertainment"



areg chshare i.year, ab(gvkey)
predict chshare_resid, resid
areg frac_of_category_sales i.year, ab(gvkey)
predict frac_resid, resid

label var frac_resid "Fraction of Category Spending Within City"
label var chshare_resid "Firm Churn Within-City"
label var frac_of_category_sales "Fraction of Category Spending in City"

gen indic = 1

STOP STOP STOP 

****************************************************************
****Tables and Figures in Paper
reghdfe chshare indic if frac_of_category_sales!=., ab( year city_code)
outreg2 using "C:/Users/srb834/Dropbox/Firm Customer Bases/Paper/Tables/Churn_Concentration.tex", title() drop(indic) tex(landscape pr frag) drop() label addtext(Year FE, YES, City FE, YES, Category FE, NO, Firm FE, NO) ctitle("Churn") nocons replace
reghdfe chshare frac_of_category_sales, ab( year city_code)
outreg2 using "C:/Users/srb834/Dropbox/Firm Customer Bases/Paper/Tables/Churn_Concentration.tex", title() keep() tex(landscape pr frag) drop() label addtext(Year FE, YES, City FE, YES, Category FE, NO, Firm FE, NO) ctitle("Churn") nocons 

reghdfe chshare indic if frac_of_category_sales!=., ab(category_code year city_code)
outreg2 using "C:/Users/srb834/Dropbox/Firm Customer Bases/Paper/Tables/Churn_Concentration.tex", title() drop(indic) tex(landscape pr frag) drop() label addtext(Year FE, YES, City FE, YES, Category FE, YES, Firm FE, NO) ctitle("Churn") nocons 
reghdfe chshare frac_of_category_sales, ab(category_code year city_code)
outreg2 using "C:/Users/srb834/Dropbox/Firm Customer Bases/Paper/Tables/Churn_Concentration.tex", title() keep() tex(landscape pr frag) drop() label addtext(Year FE, YES, City FE, YES, Category FE, YES, Firm FE, NO) ctitle("Churn") nocons 

reghdfe chshare indic if frac_of_category_sales!=., ab(gvkey year city_code)
outreg2 using "C:/Users/srb834/Dropbox/Firm Customer Bases/Paper/Tables/Churn_Concentration.tex", title() drop(indic) tex(landscape pr frag) drop() label addtext(Year FE, YES, City FE, YES, Category FE, YES, Firm FE, YES) ctitle("Churn") nocons 
reghdfe chshare frac_of_category_sales, ab(gvkey year city_code)
outreg2 using "C:/Users/srb834/Dropbox/Firm Customer Bases/Paper/Tables/Churn_Concentration.tex", title() keep() tex(landscape pr frag) drop() label addtext(Year FE, YES, City FE, YES, Category FE, YES, Firm FE, YES) ctitle("Churn") nocons 
	

binscatter chshare_resid frac_resid if frac_resid<.05 & frac_resid>-.05 & Store_Type=="Regular Purchases" , by(Store_Type) mcolor(maroon) ytitle(Firm Churn Within-City) xtitle(Fraction of Category Spending Within City)
graph export "C:\Users\srb834\Dropbox\Firm Customer Bases\Paper\Figures\churn_and_local_sales_fraction_onlyregular.png", replace

binscatter chshare_resid frac_resid if frac_resid<.05 & frac_resid>-.05 , by(Store_Type) ytitle(Firm Churn Within-City) xtitle(Fraction of Category Spending Within City)
graph export "C:\Users\srb834\Dropbox\Firm Customer Bases\Paper\Figures\churn_and_local_sales_fraction_all.png", replace

binscatter chshare_resid frac_resid if frac_resid<.05 & frac_resid>-.05 & Store_Type!="Loyalty or Infrequent", by(Store_Type) ytitle(Firm Churn Within-City) xtitle(Fraction of Category Spending Within City) msymbol(Oh)
graph export "C:\Users\srb834\Dropbox\Firm Customer Bases\Paper\Figures\churn_and_local_sales_fraction.png", replace




****************************************************************
****Add in City-level attributes for some analysis

merge m:1 city using geocorr_intermediate.dta
drop if _merge==2
drop _merge
gen lpop = ln(pop10*10)
gen lhhinc = ln(avghhincome)

label var lhhinc "ln(City HH Income)"
label var avgage "Average Age in City"
label var lpop "ln(City Population)"
label var pctcollegedeg "Fraction College Degree in City"
label var pctnonwhite "Fraction Non-White in City"


reghdfe chshare lpop   , ab(gvkey year)
outreg2 using "C:/Users/srb834/Dropbox/Firm Customer Bases/Paper/Tables/Churn_Local_Dem.tex", title() keep() tex(landscape pr frag) drop() label addtext(Year FE, YES, Firm FE, YES) ctitle("Churn") nocons replace
reghdfe chshare lpop  avgage  pctcollegedeg pctnonwhite , ab(gvkey year)
outreg2 using "C:/Users/srb834/Dropbox/Firm Customer Bases/Paper/Tables/Churn_Local_Dem.tex", title() keep() tex(landscape pr frag) drop() label addtext(Year FE, YES, Firm FE, YES) ctitle("Churn") nocons 
reghdfe chshare lpop  avgage  pctcollegedeg pctnonwhite frac_of_category_sales, ab(gvkey year)
outreg2 using "C:/Users/srb834/Dropbox/Firm Customer Bases/Paper/Tables/Churn_Local_Dem.tex", title() keep() tex(landscape pr frag) drop() label addtext(Year FE, YES, Firm FE, YES) ctitle("Churn") nocons 


